<?php

class msell_sso extends CI_Model {
    
    public function __construct()
       {
            parent::__construct(); //updated by: alfin akhret
            $this->load->library('serversidedatatables');   
       }

    //ambil record pada table t_trx_sell_in berdasarkan id
    public function getById($id) {
        $id = intval($id);

        // $query = $this->db->where('sell_in_id', $id)->limit(1)->get('t_trx_sell_in');
        $this->db->join("t_mtr_top","t_mtr_top.top_id = t_trx_sell_in.top_id");
        $query = $this->db->get_where('t_trx_sell_in', array('sell_in_id' => $id)); //edited by: alfin
        // echo $id; die();

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return array();
        }
    }

    //ambil record pada table t_trx_sell_in berdasarkan id
    public function getDetailProgram($sid, $type, $sales_id) {

        $listProgram = array();
        $listProgram['concate'] = "";
        // $listProgram['list'] = "";
        $jj = 0;

        if ($type == "ho") {
            $listProgram['concate'] = $this->getSellInProgHo($sid);
            if (count($listProgram['concate']) > 0) {
                $hoList = explode(", ", $listProgram['concate']);

                    foreach ( $hoList as $programHo ) {
                        // echo $programHo. ' ';
                        $query = $this->db->select('program_id, caption');
                        $query = $this->db->where('program_id', $programHo);
                        $query = $this->db->get( 't_mtr_program' );
                        $listProgram[] = $query->result_array();
                    }
            }
            
        } else {
            $listProgram['concate'] = $this->getSellInProgCluster($sid, $sales_id);
            if (count($listProgram['concate']) > 0) {
                $clList = explode(", ", $listProgram['concate']);

                    foreach ( $clList as $programCluster ) {
                        $query = $this->db->select('program_id, caption');
                        $query = $this->db->where('program_id', $programCluster);
                        $query = $this->db->get( 't_mtr_program' );
                        $listProgram[] = $query->result_array();
                    }
            }
        }

        return $listProgram;
        // return $holist. $clList;
    }

    //ambil record pada table t_mtr_item dan t_mtr_item_group
    public function getItemDetail($id) {
        $listItem = array();
        // ******** Mod by anggwa@gmail.com 20120504 ******** //
        // $this->db->select('item_name, item_code, item_group_id');
        // $this->db->where('iccid', $id);
        // $q = $this->db->get('t_mtr_item');
        // $listItem = $q->result_array();	
        // $this->db->select('item_group_name');
        // $this->db->where('item_group_id', $listItem[0]['item_group_id']);
        // $q = $this->db->get('t_mtr_item_group');
        // $q = $q->result_array();
        // $listItem[0]['item_group_name'] = $q[0]['item_group_name'];

        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        $query = $dbconn->query("SELECT imp.item_name, itm.mdn,  imp.item_code, grp.item_group_id, grp.item_group_name " .
                "FROM t_mtr_item itm " .
                "JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code " .
                "JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id " .
                "WHERE itm.iccid = '$id' LIMIT 1");
        //$query = $dbconn->query($sql);		
        if ($query->num_rows() > 0) {
            $listItem = $query->result_array();
        } else {
            return array();
        }
        return $listItem;
    }

    //ambil record-record yang berkaitan pada table t_trx_sell_in
    public function getAllSellin($cluster_id='') {
        
        $res = array();
        $dbconn = $this->load->database("default", TRUE);

        //updated by alfin akhret 
        //SSP-kan sell in datagrid
        $sIndexColumn = "sell_in_id"; //index column untuk kecepatan akses data
        $defaultOrder = "0"; //default order berdasarkan field yg ada pada $aColumns
        $userlogin = $this->session->userdata('username'); //user credentials
        $aColumns = array("created_on", "sell_in_id", "user_name"); //field2 yg akan ditampilkan pada dataTab
        $flag = '';
        //SQL query 
        $inputQuery = "
            SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
            FROM v_ssp_sell_in where cluster_id = $cluster_id";
        //execute datatables serversideprocess
        return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn, 'asc');

        // if ($query->num_rows() > 0) {
        //     foreach ($query->result() as $res) {
        //         $res->getNikName = $this->getNikName($res->user_id);
        //         //mod by anggwa@gmail.com 20120505
        //         //$res->tanggalJual = $this->getNikName($res->user_id);
        //     }
        //     return $query->result();
        // } else {
        //     return array();
        // }
    }

    //edited by Alfin akhret
    function getAllSellinCurrentUser()
    {
        // $res = array();
        // $dbconn = $this->load->database("default", TRUE);
        // $userlogin = $this->session->userdata('username');
        // $query = $dbconn->query("SELECT to_char(created_on, 'DD Mon YYYY') as created_on, sell_in_id, user_id FROM t_trx_sell_in
        //         WHERE DATE_PART('day', now() - created_on::timestamp) 
        //         BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month'))
        //         AND user_id = '$userlogin'
        //         ORDER BY sell_in_id DESC, created_on desc");
        // if ($query->num_rows() > 0) {
        //     foreach ($query->result() as $res) {
        //         $res->getNikName = $this->getNikName($res->user_id);
        //         //mod by anggwa@gmail.com 20120505
        //         //$res->tanggalJual = $this->getNikName($res->user_id);
        //     }
        //     return $query->result();
        // } else {
        //     return array();
        // }

        $res = array();
        $dbconn = $this->load->database("default", TRUE);

        //updated by alfin akhret 
        //SSP-kan sell in datagrid
        $sIndexColumn = "sell_in_id"; //index column untuk kecepatan akses data
        $defaultOrder = "0"; //default order berdasarkan field yg ada pada $aColumns
        $userlogin = $this->session->userdata('username'); //user credentials
        $aColumns = array("created_on", "sell_in_id", "user_name"); //field2 yg akan ditampilkan pada dataTab
        $flag = '';
        //SQL query 
        $inputQuery = "
            SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
            FROM v_ssp_sell_in_smile where user_id = '".$this->session->userdata('username')."'";

        return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn, 'asc');

    }

    //ambil outlet name dari user yang bersangkutan
    function getOrderInfo($outlet_id = "") {
        //ambil out

        $this->db->select('outlet_name');
        $this->db->where('outlet_id', $outlet_id);
        $q = $this->db->get('t_mtr_outlet');

        if ($q->num_rows() > 0) {
            $q = $q->result_array();
            return $q[0]['outlet_name'];
        }
        else
            return "No Outlet Name";
    }

    //lakukan insert ke table t_trx_sell_in
    function saveSell() {

        $dblokal = $this->load->database("default", true);
        $sell_date = $this->input->post("sell_date");
        $top_id = $this->input->post("term_of_payment");

        /* yaddisurahman@gmail.com -- 19 Mei
          sales_id 	=> 92000418 | Apriana
          channel_id 	=> JBL20689 | ABOET CELL
          need to be explode()
         */
        $sid = $this->input->post("sales_person");
        $sales_id = explode(" ", $sid);
        $cid = $this->input->post("outlet_name");
        $channel_id = explode(" ", $cid);

        $data = array(
            "top_id" => $top_id,
            "sell_in_date" => $sell_date,
            "created_by" => $this->session->userdata('username'),
            "updated_by" => $this->session->userdata('username'),
            "user_id" => $sales_id[0],
            "channel_id" => $channel_id[0],
            "acess_via" => "web",
            "istatus" => 1
        );
        $dblokal->insert("t_trx_sell_in", $data);
        $last = $this->db->insert_id();

        // 20120514 anggwa@gmail.com -> log
        $this->mglobal->saveLog('sell-in', 'create', $last);

        return $last;
    }

    //lakukan insert ke table t_trx_sell_in_program
    function saveSellInProgram($lastSid) {
        $this->db->trans_begin();

        $dblokal = $this->load->database("default", true);
        $sell_date = $this->input->post("sell_date");
        $sales_id = $this->input->post("sales_person");

        // $listProgram = array();
        // $listProgram = $this->input->post("program_ho") . $this->input->post("program_cluster");
        // $program_id = explode(",", $listProgram);

        /*
        ** @contoh variable      : 11# Gido SP, 14# Outlet Expansion,
        ** 11                    : ID Program
        ** Gido SP               : Nama Program
        ** 11# Gido SP,          : Program 1
        ** 14# Outlet Expansion, : Program 2
        */

        $program_id  = array();
        $hoProgTemp1 = explode(",", $this->input->post("program_ho"));
        $clProgTemp1 = explode(",", $this->input->post("program_cluster"));

        for ($i=0; $i < count($hoProgTemp1) ; $i++) { 
            $hoProgTemp[]  = explode("#", $hoProgTemp1[$i]);
            $program_id[]  =  $hoProgTemp[$i][0];
        }
        unset($program_id[$i-1]);
        for ($j=0; $j < count($clProgTemp1); $j++) { 
            $clProgTemp[]  = explode("#", $clProgTemp1[$j]);
            $program_id[]  =  $clProgTemp[$j][0];
        }
        unset($program_id[$i+$j-1]);


        $data = array(
            "sell_in_id" => $lastSid,
            "created_by" => $this->session->userdata('username'),
            "updated_by" => $this->session->userdata('username'),
            "created_on" => $sell_date,             
            "updated_on" => $sell_date,             
        );
        // print_r($program_id);
        foreach ($program_id as $key) {
            $data["program_id"] = $key;

            $dblokal->insert("t_trx_sell_in_program", $data);

            // 20120514 anggwa@gmail.com -> log
            $last = $this->db->insert_id();
            $this->mglobal->saveLog('sell-in-detail', 'create', '(header=' . $lastSid . ' detail=' . $last . ' program_id=' . $key . ')');
        }
    }

    //lakukan insert ke table t_trx_sell_in_detail
    function saveSellInDetail($sid, $iccid) {

        $dblokal = $this->load->database("default", true);
        // $iccid = $this->input->post("iccid");	// yaddisurahman
        $sell_date = $this->input->post("sell_date");

        $data = array(
            // "iccid" => $iccid[$i], 
            "iccid" => $iccid,
            "sell_in_id" => $sid,
            "istatus" => 1,
            "created_by" => $this->session->userdata('username'),
        );

        $dblokal->insert("t_trx_sell_in_detail", $data);


        // 20120514 anggwa@gmail.com -> log
        $last = $this->db->insert_id();
        $this->mglobal->saveLog('sell-in-detail', 'create', '(header=' . $sid . ' detail=' . $last . ' iccid=' . $iccid . ')');

        //update istatus pada table t_trx_scan_in_detail menjadi 2
        $data2 = array(
            'istatus' => 2,
            "transaction_type" => 'Sell In',
            "transaction_id" => $sid
        );
        $this->db->update('t_trx_scan_in_detail', $data2, array('iccid' => $iccid));
    }

    //ambil nama territory dari id user yang bersangkutan
    public function getClusterName($id = '') {

        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_territory_path(f_get_cluster('$id')) as territory_name, f_get_cluster('$id') as territory_id";
        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return array();
        }
        // $this->db->select('territory_name, territory_id');
        // $this->db->from('t_mtr_territory');
        // $this->db->where('user_id', $id);
        // $this->db->limit(1, 0);
        // $query = $this->db->get();
        // return $query->result_array();
    }

    //ambil daftar sales id yang terdaftar pada territory yang sama dengan user yang sedang login
    public function getSalesID($cluster_id) {
        $this->db->distinct();
        $this->db->select('usr.user_id, usr.user_group_id, initcap(usr.user_name) as user_name, usr.user_group_id, grp.user_group_caption');
        // $this->db->group_by('usr.user_id');
        $this->db->from('t_mtr_user AS usr');
        $this->db->join('t_mtr_user_group AS grp', 'usr.user_group_id = grp.user_group_id AND usr.istatus = 1 AND grp.having_stock = 1');
        $this->db->join('t_trx_scan_in AS scan', 'usr.user_id = scan.user_id AND scan.istatus = 1');
        $this->db->where("f_get_cluster(usr.user_id) = $cluster_id");
        $query = $this->db->get();
        return $query->result();
    }

  /**
   * New way to display sales team which having stocks
   * @param  integer $cluster_id 
   * @return array             [description]
   */
  public function sell_in_sales_team( $cluster_id ) {
    $this->db->distinct();
    $this->db->select('uu.user_id, initcap(uu.user_name) as user_name, uu.user_group_id, gg.user_group_caption');
    $this->db->join('t_mtr_user_group AS gg', 'uu.user_group_id = gg.user_group_id AND uu.istatus = 1 AND gg.having_stock = 1');
    $this->db->join('t_trx_scan_in AS ss', 'uu.user_id = ss.user_id');
    $this->db->join('t_trx_scan_in_detail AS sd', 'sd.scan_in_id = ss.scan_in_id AND sd.istatus = 1');
    $this->db->where("f_get_cluster(uu.user_id)", $cluster_id);
    $this->db->where("f_get_check_jc(uu.user_id,uu.user_group_id)", 1);
    // $this->db->where_in("gg.user_group_id", $user_group);
    $this->db->order_by("uu.user_group_id, user_name", "asc");
    $query = $this->db->get("t_mtr_user AS uu");
    return $query->result();
  }


    // populate latest scaned items
    function getItems($sid) {
        $this->db->select("itm.iccid, itm.item_code, scan.user_id, itm.mdn, grp.item_group_name, itm.item_name");
        $this->db->select("f_get_price(itm.iccid, '$sid') as getPrice", false);
        $this->db->from("t_trx_scan_in_detail AS scand");
        $this->db->join("t_trx_scan_in AS scan", "scand.scan_in_id = scan.scan_in_id AND scand.istatus = 1");
        $this->db->join("t_mtr_item AS itm", "scand.iccid = itm.iccid");
        $this->db->join("t_mtr_item_group AS grp", "itm.item_group_id = grp.item_group_id");
        $this->db->where("scan.user_id", $sid);
        $query = $this->db->get();

        return $query->result_array();
    }

    //ambil record-record yang berkaitan dengan proses pembuatan sell in
    function getSell($iccid) {
        $sales_id = $this->input->post("sales_person");

        $this->db->select('itm.item_code, itm.mdn, grp.item_group_name, itm.item_name, itm.reseller_price, itm.default_price');
        $this->db->from('t_trx_scan_in_detail AS scand');
        $this->db->join("t_trx_scan_in AS scan", "scand.scan_in_id = scan.scan_in_id AND scand.istatus = 1");
        $this->db->join("t_mtr_item AS itm", "scand.iccid = itm.iccid AND itm.iccid = '$iccid'");
        $this->db->join('t_mtr_item_group AS grp', 'itm.item_group_id = grp.item_group_id');
        $query = $this->db->get();
        //return $query->result_array();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $res) {
                $res->getPrice = $this->getPrice($iccid, $sales_id);
            }
            return $query->result();
        } else {
            return array();
        }
    }

    function checkUserGroup($sales_id) {
        $this->db->select('grp.user_group_name');
        $this->db->from('t_mtr_user as usr');
        $this->db->join('t_mtr_user_group as grp', 'usr.user_group_id = grp.user_group_id');
        $this->db->where('usr.user_id', $sales_id);
        $q = $this->db->get();

        if ($q->num_rows() > 0) {
            $q = $q->result_array();
            return $q[0]['user_group_name'];
        } else {
            return array();
        }
    }

    public function get_outlet_list($user_group_id, $user_id)
    {
      $where1 = "(tt.user_id = '$user_id' OR (tt.parent_id = f_get_cluster('$user_id') AND aa.agent_selling_type = 2 ) )";
      $this->db->select('jc.channel_id, f_get_channel_name(jc.channel_id) as channel_name, jc.territory_id');
      $this->db->join('t_mtr_territory tt', 'tt.territory_id = jc.territory_id');
      $this->db->join('t_mtr_agent aa', 'aa.territory_id = jc.territory_id' ,'left');
      $this->db->where('jc.user_id', $user_id);
      $this->db->where('jc.yyyymm', date('Ym'));
      $this->db->where('jc.d'.date('j'), 1);
      $this->db->where($where1);
      $query = $this->db->get('t_jc_'.$user_group_id.' jc');

      return $query->result();
    }

  // untuk mendapatkan program, pastikan jenis partner apakah outlet atau agent.
  // treatment untuk agent dan lokasi mobsell, keduanya dianggap sama
  // untuk sementara, program hanya berlaku untuk outlet dan agent
  // sedangkan program untuk sfa/ds diabaikan berhubung dengan perkembangan user_group tidak bisa dikontrol
  // @yaddis -- 01/28/2013
  public function get_program( $user_group_id, $channel_id ) {

    $this->db->where('outlet_id', $channel_id);
    $this->db->from('t_mtr_outlet');
    $numrow = $this->db->count_all_results();

    // Jika hasil query != 0, maka dapat dipastikan partner tersebut adalah outlet dan program_type nya adalah 1
    // selain itu adalah agent/mobsell dan program_type nya adalah 2
    if($numrow != 0) 
      { $program_type = array(1,3); }
    else
      { $program_type = array(2,4); }

    $this->db->select("parent_id");
    $this->db->where("territory_id", "f_get_territory_id_by_channel_id('$channel_id')", false );
    $cluster_id = $this->db->get("t_mtr_territory")->row()->parent_id;

    $this->db->select("program_id, caption, description, territory_id");
    $this->db->where_in("program_type_id", $program_type );
    $this->db->where_in("territory_id", array(1, $cluster_id) );
    $this->db->order_by('program_id', 'asc');
    $this->db->where("istatus", 1);
    $query = $this->db->get("t_mtr_program");
    return $query->result();
  }

    function getProgram($sid, $program) {
        $userGroup = $this->checkUserGroup($sid);
        if ($userGroup == "Direct Sales") {
            $pType = 4;
        } else if ($userGroup == "Smart Fren Ambassador") {
            $pType = 3;
        } else {
            return array();
        }

        $userLogin = $this->session->userdata('username');
        $dbconn = $this->load->database("default", TRUE);
        if ($program == "ho") {
            $sql = "select program_id as mvalue, caption as mdisplay
					from t_mtr_program
					where territory_id = 1 and program_type_id = '$pType' and istatuss = 1
					order by caption asc";
        } else {
            $sql = "select program_id as mvalue, caption as mdisplay
					from t_mtr_program
					where territory_id = f_get_cluster('$userLogin') and program_type_id = '$pType' and sistatus = 1
					order by caption asc";
        }
        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
    }

    //ambil daftar iccid dari barang-barang yang diambil sales tadi pagi pada cluster yang bersangkutan, untuk ditampilkan pada textbox autocomplete
    function getRelatedIccid($salesId = "", $term = "") {

        $this->db->distinct();
        $this->db->select('scand.iccid');
        $this->db->from('t_trx_scan_in_detail as scand');
        $this->db->like('scand.iccid', $term, 'after');
        $this->db->join('t_trx_scan_in as scan', 'scan.scan_in_id = scand.scan_in_id');
        $this->db->where('scan.user_id', $salesId);
        $this->db->where('scand.istatus', 1);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return array();
        }
    }

    //get nik name from user
    function getNikName($userID) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_nik_name('$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_nik_name'];
        } else {
            return array();
        }
    }

    //get item price for each sales
    function getPrice($iccid = "", $userID = "") {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT * from f_get_price('$iccid','$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_price'];
        } else {
            return array();
        }
    }

    public function getSellInProgHo($SellInId) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_sell_in_program_ho('$SellInId')";

        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_sell_in_program_ho'];
        } else {
            return array();
        }
    }

    public function getSellInProgCluster($SellInId, $SalesId) {
        //Di Blok dulu biar ga error
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_sell_in_program_cluster('$SellInId','$SalesId')";
        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_sell_in_program_cluster'];
            return $query->result();
        } else {
            return array();
        }

        return "1";
    }

    public function getPartner($channelId) {
        //Di Blok dulu biar ga error
        $dbconn = $this->load->database("default", TRUE);
        $query = $dbconn->query("SELECT f_get_channel_name('$channelId')");
        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_channel_name'];
            //return $query->result();
        } else {
            return array();
        }
    }

    public function termins() {
        $query =$this->db->get_where("t_mtr_top",array("istatus" => 1));
        return $query->result();
    }

    ## yaddis@nutech
    public function validate_item($iccid, $sales_id="", $channel_id="")
    {
        $this->db->select('iccid');
        $this->db->where("t_trx_scan_in_detail.iccid", $iccid );
        $this->db->where("t_trx_scan_in_detail.istatus", 1 );
        $query = $this->db->get("t_trx_scan_in_detail");
        if ( $query->num_rows() == 0 ) 
            { return null; }
        else
            { return $iccid; }
    }

    ## yaddis@nutech
    public function save_sell_in($sales_id, $channel_id, $termin_type, $trans_date) {
        $data = array(
            "top_id" => $termin_type,
            "sell_in_date" => $trans_date,
            "created_by" => $this->session->userdata('username'),
            "user_id" => $sales_id,
            "channel_id" => $channel_id,
            "acess_via" => "web",
            "istatus" => 1
        );
        $this->db->insert("t_trx_sell_in", $data);
        $sell_in_id = $this->db->insert_id();
        // $this->mglobal->saveLog('sell-in', 'create', $sell_in_id);
        return $sell_in_id;
    }

    ## yaddis@nutech
    public function save_sell_program($sell_in_id, $program_id, $sell_date) {
        $data = array(
            "sell_in_id" => $sell_in_id,
            "program_id" => $program_id,
            "created_by" => $this->session->userdata('username'),
            "created_on" => $sell_date
        );
        $this->db->insert("t_trx_sell_in_program", $data);
        return $this->db->insert_id();
    }

    ## yaddis@nutech
    public function save_sell_detail($sell_in_id, $iccid) {
        $data = array(
            "iccid" => $iccid,
            "sell_in_id" => $sell_in_id,
            "istatus" => 1,
            "created_by" => $this->session->userdata('username'),
        );

        $this->db->insert("t_trx_sell_in_detail", $data);
        
        $last = $this->db->insert_id();
        $this->db->update('t_trx_scan_in_detail', array('istatus' => 2, "transaction_type" => 'Sell In', "transaction_id" => $sell_in_id), array('iccid' => $iccid));
        // $this->mglobal->saveLog('sell-in-detail', 'create', '(header=' . $sell_in_id . ' detail=' . $last . ' iccid=' . $iccid . ')');
    }

    public function save_eload_sell($eload) {
      $this->db->trans_begin();
      
        $this->db->insert_batch('t_trx_eload', $eload);
        $test= $this->db->insert_id();
        $this->mglobal->saveLog('eload_sell', 'Sell eload to '.count($eload).' channel', $test);
      if ($this->db->trans_status() === FALSE)
      {
        $this->db->trans_rollback();
        return false;
      }
      else
      {
        $this->db->trans_commit();
        return true;
      }
    }

  public function eload_detail($eload_id)
  {
    $this->db->select("eload_id, f_get_territory_path(f_get_cluster(created_by)) as territory_name, amount", false);
    $this->db->select("concat(channel_id, ' | ', f_get_channel_name(channel_id)) as channel_name", false);
    $this->db->select("CASE WHEN ( f_get_group_caption_by_user (created_by) = 'SFA' ) THEN concat ( created_by, ' | ', f_get_user (created_by)) WHEN ( f_get_group_caption_by_user (created_by) = 'DS' ) THEN concat ( created_by, ' | ', f_get_user (created_by)) ELSE concat ( sales_id, ' | ', f_get_user (sales_id)) END AS sales_name",false);
    $this->db->select("CASE WHEN (istatus = 1) THEN 'Unpaid' ELSE 'Paid' END AS status", false);
    $this->db->select('CASE WHEN (sell_date is not null) then sell_date ELSE date(created_on) END AS "sell_date" ', false);
    $this->db->where('eload_id', $eload_id);
    $query = $this->db->get('t_trx_eload');
    return $query->row();
  }

  public function close_eload($eload_id)
  {
    $this->db->trans_begin();
    $data = array(
        'updated_by' => $this->session->userdata('username'), 
        'updated_on' => date('Y-m-d H:i:s'),
        'istatus'    => 2
      );
    $this->db->where('eload_id', $eload_id);
    $this->db->update('t_trx_eload', $data);
    if ($this->db->trans_status() === FALSE)
    {
      $this->db->trans_rollback();
      return false;
    }
    else
    {
      $this->db->trans_commit();
      return true;
    }
  }

  /**
   * Ini query untuk menampilkan data transaksi klaim kartu garansi (KKG)
   * Seperti biasa ada parameter $cluster_id
   * @param  integer $cluster_id
   * @return array
   */
  public function get_all_eload($cluster_id) {
    $sIndexColumn = "eload_id"; //index column untuk kecepatan akses data
    $defaultOrder = "0"; //default order berdasarkan field yg ada pada $aColumns
    $userlogin = $this->session->userdata('username'); //user credentials
    $flag = '';
    // $aColumns = array("agent_id", "agent_name", "agent_address", "image_path", "lon", "lat"); //field2 yg akan ditampilkan pada dataTables
    $aColumns = array("eload_id", "sell_date", "channel_name", "eload_number", "amount", "sales_name", "status"); //field2 yg akan ditampilkan pada dataTables
    $inputQuery = "SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
    FROM v_ssp_eload where cluster_id = $cluster_id";
    return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn, 'desc');
  }

}

//end class